create table studentinfo
(
    sno      varchar(50) primary key,
    sname    varchar(50) not null,
    sage     int check (sage >= 18 and sage <= 40),
    sbirth   date        not null,
    saddress varchar(50) default '地址不详'
);


create table score
(
    id      int primary key,
    wirexam number(4, 1) not null,
    labexam number(4, 1) not null,
    sno     varchar(50) references studentinfo (sno)
);

-- 创建 序列 记录编号
create sequence s_stu
    start with 1001;
-- 学生编号以S1001开始，插入测试数据 能随机尽量随机
insert into studentinfo (sno, sname, sage, sbirth, saddress)
values (concat('S', s_stu.nextval),
        concat('张', DBMS_RANDOM.STRING('p', 2)),
        DBMS_RANDOM.VALUE(18, 40),
        ADD_MONTHS(sysdate, trunc(DBMS_RANDOM.VALUE(1002, 1028))),
        DBMS_RANDOM.STRING('i', 20));

select *
from studentinfo;

-- 创建序列，记录分数的 id
create sequence s_score;
insert into score (id, wirexam, labexam, sno)
values (s_score.nextval,
        DBMS_RANDOM.VALUE(1, 1000) + DBMS_RANDOM.VALUE(1, 10) * .01,
        DBMS_RANDOM.VALUE(1, 1000) + DBMS_RANDOM.VALUE(1, 10) * .01,
        concat('S', trunc(DBMS_RANDOM.VALUE(-240, -264))));
select *
from score;
select concat('S', DBMS_RANDOM.VALUE(1002, 1028))
from DUAL;


--2：查询姓名中包含5字的学生学号，姓名和年龄及生日和分数
select stu.sno                                                     学号,
       sname                                                       姓名,
       abs(extract(year from sysdate) - extract(year from sbirth)) 年龄,
       sbirth                                                      生日,
       sc.wirexam                                                  笔试,
       sc.labexam                                                  机试
from studentinfo stu,
     score sc
where sname like '%5%'
  and sc.sno = stu.sno;

-- 更新 生日
update studentinfo
set sbirth=ADD_MONTHS(sbirth, trunc(DBMS_RANDOM.VALUE(-240, -264)));
-- 更新年龄
update studentinfo
set sage=abs(extract(year from sysdate) - extract(year from sbirth));

--3：查询年龄在18到22之间的学生学号，姓名和年龄
select sno 学号, sname 姓名, abs(extract(year from sysdate) - extract(year from sbirth)) 年龄
from studentinfo
where abs(extract(year from sysdate) - extract(year from sbirth)) >= 18
  and abs(extract(year from sysdate) - extract(year from sbirth)) <= 22;


--4：查询所有学生的学号，姓名和成绩
select stu.sno                                   学号,
       stu.sname                                 姓名,
       (nvl(sc.wirexam, 0) + nvl(sc.labexam, 0)) 成绩
from studentinfo stu,
     score sc
where stu.sno = sc.sno(+);



--5：查询没有考试的学生的学号和姓名
-- 所有的学生 27
select sno, sname
from studentinfo;
-- 已经考试的学号 11
select distinct sno
from score;
-- 综合
select sno, sname
from studentinfo
where sno not in (select distinct sno from score);


--6：查询笔试成绩大于机试成绩的学生学号姓名及成绩
select stu.sno                                     学号,
       stu.sname                                   姓名,
       (nvl(sco.wirexam, 0) + nvl(sco.labexam, 0)) 成绩
from studentinfo stu,
     score sco
where stu.sno = sco.sno
  and sco.wirexam > sco.labexam;


--7：查询所有学生的学号，姓名和成绩并从查询结果中取出第二行到第四行的数据
-- 查询所有学生的学号，姓名和成绩，排序
select stu.sno                                     学号,
       stu.sname                                   姓名,
       (nvl(sco.wirexam, 0) + nvl(sco.labexam, 0)) 成绩
from studentinfo stu,
     score sco
where stu.sno = sco.sno
order by 成绩 desc;

-- 防止 排序后  ROWNUM 混乱，包装一下
select 学号, 姓名, 成绩, ROWNUM r
from (select stu.sno                                     学号,
             stu.sname                                   姓名,
             (nvl(sco.wirexam, 0) + nvl(sco.labexam, 0)) 成绩
      from studentinfo stu,
           score sco
      where stu.sno = sco.sno
      order by 成绩 desc);

-- 通过子查询实现分页
select 学号, 姓名, 成绩, r
from (select 学号, 姓名, 成绩, ROWNUM r
      from (select stu.sno                                     学号,
                   stu.sname                                   姓名,
                   (nvl(sco.wirexam, 0) + nvl(sco.labexam, 0)) 成绩
            from studentinfo stu,
                 score sco
            where stu.sno = sco.sno
            order by 成绩 desc)) t
where t.r >= 2
  and t.r <= 4;


--8：获取总成绩排名前3和排名倒数3的所有学生的信息
-- 所有学生的信息 和 总成绩,从大到小
select stu.sno                   学号,
       stu.sname                 姓名,
       stu.sage                  年龄,
       stu.sbirth                生日,
       stu.saddress              地址,
       sco.wirexam + sco.labexam 总成绩
from studentinfo stu,
     score sco
where stu.sno = sco.sno
order by (sco.wirexam + sco.labexam) desc;
-- 带有 ROWNUM 的
select one.*, ROWNUM R
from (
         select stu.sno                   学号,
                stu.sname                 姓名,
                stu.sage                  年龄,
                stu.sbirth                生日,
                stu.saddress              地址,
                sco.wirexam + sco.labexam 总成绩
         from studentinfo stu,
              score sco
         where stu.sno = sco.sno
         order by (sco.wirexam + sco.labexam) desc
     ) one;

-- 获取前三
select 学号, 姓名, 年龄, 生日, 地址, 总成绩
from (select one.*, ROWNUM R
      from (
               select stu.sno                   学号,
                      stu.sname                 姓名,
                      stu.sage                  年龄,
                      stu.sbirth                生日,
                      stu.saddress              地址,
                      sco.wirexam + sco.labexam 总成绩
               from studentinfo stu,
                    score sco
               where stu.sno = sco.sno
               order by (sco.wirexam + sco.labexam) desc
           ) one) one1
where one1.R >= 1
  and one1.R <= 3;

-- 获取后三
select 学号, 姓名, 年龄, 生日, 地址, 总成绩
from (select one.*, ROWNUM R
      from (
               select stu.sno                   学号,
                      stu.sname                 姓名,
                      stu.sage                  年龄,
                      stu.sbirth                生日,
                      stu.saddress              地址,
                      sco.wirexam + sco.labexam 总成绩
               from studentinfo stu,
                    score sco
               where stu.sno = sco.sno
               order by (sco.wirexam + sco.labexam)
           ) one) one1
where one1.R >= 1
  and one1.R <= 3;

-- 联合
select 学号, 姓名, 年龄, 生日, 地址, 总成绩
from (select 学号, 姓名, 年龄, 生日, 地址, 总成绩
      from (select one.*, ROWNUM R
            from (
                     select stu.sno                   学号,
                            stu.sname                 姓名,
                            stu.sage                  年龄,
                            stu.sbirth                生日,
                            stu.saddress              地址,
                            sco.wirexam + sco.labexam 总成绩
                     from studentinfo stu,
                          score sco
                     where stu.sno = sco.sno
                     order by (sco.wirexam + sco.labexam) desc
                 ) one) one1
      where one1.R >= 1
        and one1.R <= 3)
union All
(select 学号, 姓名, 年龄, 生日, 地址, 总成绩
 from (select one.*, ROWNUM R
       from (
                select stu.sno                   学号,
                       stu.sname                 姓名,
                       stu.sage                  年龄,
                       stu.sbirth                生日,
                       stu.saddress              地址,
                       sco.wirexam + sco.labexam 总成绩
                from studentinfo stu,
                     score sco
                where stu.sno = sco.sno
                order by (sco.wirexam + sco.labexam)
            ) one) one1
 where one1.R >= 1
   and one1.R <= 3)
order by 总成绩 desc;


--9：获取所有学生的姓的个数  按人数排序
select *
from studentinfo;

-- 获取所有的姓
select distinct substr(sname, 1, 1)
from studentinfo;
-- 统计每个姓的个数
select substr(sname, 1, 1) snamesub, count(*) 总数
from studentinfo
group by substr(sname, 1, 1);


--10：请查询出下个月过生日的学生的学号和名字及生日
select s.*, sysdate
from studentinfo s
where extract(month from sysdate) + 1 = extract(month from s.sbirth);

--11：查询平均成绩>60分的学生的成绩和学生姓名
select s.姓名, s.成绩
from v_stu_score s
where 成绩 > 60;

--12：查询每个学生的总分并求出前三名的学生信息和总分

--14:创建视图 v_stu_score 包含所有学号 姓名 成绩信息
create view v_stu_score as
select stu.sno                                   学号,
       stu.sname                                 姓名,
       (nvl(sc.wirexam, 0) + nvl(sc.labexam, 0)) 成绩
from studentinfo stu,
     score sc
where stu.sno = sc.sno(+);

select *
from v_stu_score;



select stu.sno, stu.sname, sco.wirexam, sco.labexam
from studentinfo stu,
     score sco
where stu.sno = sco.sno
  and sco.wirexam > sco.labexam;

-- 27
select *
from studentinfo;
-- 17
select *
from score;
-- 459
select *
from studentinfo,
     score;
